# 王磊的作业
import xlrd

from openpyxl import Workbook

# 文件路径
bd = xlrd.open_workbook(filename='百度合作单位-人员管理-二期.xls', encoding_override=True)
# 找到选项卡
sj = bd.sheet_by_index(0)
# 1、统计所有表格中有多少人
print('表格中一共有{}人'.format(sj.nrows - 1))
# 2、统计办电信，联通，移动的用户数量
dxsum, ydsum, ltsum, xnsum = 0, 0, 0, 0
for i in range(sj.ncols):
    if sj.col_values(i)[0] == '电话号码':
        for j in sj.col_values(i)[1:]:
            x = int(j) // 1000000000
            if x == 14 or x == 17:
                dxsum += 1
            elif x == 13:
                ydsum += 1
            elif x == 15:
                ltsum += 1
            else:
                xnsum += 1

print('电信用户{}人，移动用户{}人，联通用户{}人，有{}人可能是虚拟手机号'.format(dxsum, ydsum, ltsum, xnsum))

# 3、总公司男女人数
nan, nv = 0, 0
for i in range(sj.ncols):
    if sj.col_values(i)[0] == '性别':
        for j in sj.col_values(i)[1:]:
            if j == '男':
                nan += 1
            else:
                nv += 1

print('性别男有{}人，性别女有{}人'.format(nan, nv))

# 4、年龄超过45岁的老员工人数
age = 0
for i in range(sj.ncols):
    if sj.col_values(i)[0] == '年龄':
        for j in sj.col_values(i)[1:]:
            if int(j) > 45:
                age += 1
print('老员工有{}人'.format(age))

# 薪资高于8000元的高新人员和薪资低于3000的低薪人员
gao, di = 0, 0
for i in range(sj.ncols):
    if sj.col_values(i)[0] == '薪资':
        for j in sj.col_values(i)[1:]:
            if int(j) > 8000:
                gao += 1
            elif int(j) < 3000:
                di += 1
print('高新有{}人，低薪有{}人'.format(gao, di))

# 去传媒公司的人数数量
chuan = set('传媒')
csum = 0
for i in range(sj.ncols):
    if sj.col_values(i)[0] == '外包公司':
        for j in sj.col_values(i)[1:]:
            yuan = set(j)
            if yuan & chuan:
                csum += 1
print('传媒公司有{}家'.format(csum))

# 可能在高危地区的人数
hlj = set('黑龙')
bj = set('北京')
fj = set('福建')
sc = set('四川')
gwsum = 0
for i in range(sj.ncols):
    if sj.col_values(i)[0] == '居住地址':
        for j in sj.col_values(i)[1:]:
            dz = [x for x in j]
            for c in range(len(dz) - 2):
                dz.pop()
            di = set(dz)
            if di.issubset(hlj) or di.issubset(bj) or di.issubset(fj) or di.issubset(sc):
                gwsum += 1
print('可能在高危地区的有{}人'.format(gwsum))

# 创建表
xin = Workbook()
xinsj = xin.active
xinsj.title = '人员管理'

for j in range(sj.nrows):
    lie = []
    for i in range(sj.ncols):
        lie.append(sj.col_values(i)[j])
    xinsj.append(lie)
xin.save('new百度.xlsx')
